<cfoutput>

<cfsetting showdebugoutput="false">

<cfset data = queryNew("id,name,age,random,active,formulacol,formulacol2","integer,varchar,integer,integer,bit,varchar,varchar")>
<cfloop index="x" from="1" to="15">
   <cfset queryAddRow(data)>
   <cfset querySetCell(data,"id",x)>
   <cfset querySetCell(data,"name","User #x#")>
   <cfset querySetCell(data,"age",randRange(20,90))>
   <cfset querySetCell(data,"random",randRange(20,420))>
   <cfset querySetCell(data,"active",false)>
</cfloop>

<cfif NOT structKeyExists(form,"fieldnames")>
  <form action="#cgi.SCRIPT_NAME#" method="post" enctype="multipart/form-data">
	  <fieldset>
		File:
		<cfif findNoCase("local",cgi.HTTP_HOST)>
			(Leave blank for <a href="jxlrwtest.xls">jxlrwtest.xls</a>)<br />
			&nbsp; &nbsp; &nbsp; &nbsp; <input type="file" name="xlsfile" size="78" /><br />
		<cfelse>
			<a href="jxlrwtest.xls">jxlrwtest.xls</a><br />
		</cfif> 
	  &nbsp; Start Row: <input type="text" name="startRow" size="3" value="1" /> |
	  Max Rows: <input type="text" name="maxRows" size="3" value="15" /> (0 for all)
	  <br />
	  <input type="submit" value="View as XML" name="viewXML">
	  <input type="submit" value="View Query Dump" name="dumpQuery"> 
		</fieldset>
		<br />
	  <fieldset>
	  <H3>Embedded Excel Row Formulas Example <a href="http://cfjexcel.googlecode.com/svn/trunk/test.cfm">[source]</a></H3>
		This is the QueryNew function (for column name reference):<br />
		&nbsp; queryNew("id, name, age, random, active, formulacol, formulacol2","integer,varchar,integer,integer,bit,varchar,varchar")<br />
	  Formula: [Colum name] = [formula] <br />
	  In this case, we've got an empty field named "formulacol", and one named "formulacol2" which we'll use to store the formula results<br />
	  Here are some examples:<br />
	  &nbsp; formulacol = SUM(random + age) -- <em>This is obvious- sums the (random + age) row values</em><br />
	  &nbsp; formulacol2 = UPPER(name) -- <em>This will uppercase whatever is in "name" column</em><br />
	  Formula Col <input type="text" name="formula" size="78" value="formulacol = UPPER(name)" /><br />
	  Formula Col 2 <input type="text" name="formula" size="78" value="formulacol2 = SUM(random + age)" /> <br />
	  <input type="submit" value="Create Query => Add Formula => Create New XLS" name="testFormula">
	  <br />
	  Test Query Dump:<cfdump var="#data#">
	  </fieldset>
	</form>
<cfelse>
	<cfif findNoCase("local",cgi.HTTP_HOST)>
		<cfset xlsFilePath = form.xlsfile>
	<cfelse>
		<cfset xlsFilePath = "">
	</cfif>
	<cfif xlsfilePath eq "">
		<cfset xlsFilePath ="./jxlrwtest.xls">
	</cfif>
	<cfobject name="xls2query" component="xls2query">
	<cfif structKeyExists(form,"dumpQuery")>
		<cfdump var="#xls2query.XLS2Query(file=xlsFilePath,startRow=form.startRow,maxRows=form.maxRows)#">
	</cfif>

	<cfset xlsFile = xls2query.getXlsFile(xlsFilePath)>
	<cfset formula = "">


	<cfif structKeyExists(form,"testFormula")>
		<cfobject name="query2xls" component="query2xls" />
		<cfset queries = structNew() />
		<cfset formulas = structNew() />
		<cfset queries[1]["sheet1qry"] = data />
		<cfset formulas[1]["formulas"] = form.formula />
		<cfset queries[2]["sheet2qry"] = data />
		<cfset daXls = query2xls.queriesToXLS(queries,formulas) />
		<cfset cvalue = 'attachment;' />
		<cfset cvalue = cvalue & "filename=test.xls" />
		<cfheader name="Content-Disposition" value="#cvalue#" />
		<cfcontent type="application/msexcel" variable="#daXls#" reset="true" />
		<cfabort />
	</cfif>

	<cfif structKeyExists(form,"viewXML")>
		<cfobject name="xlsXml" component="xls2xml">
		<cfobject type="Java" action="create" name="Workbook" class="jxl.Workbook">
		<cfset workbook = xls2query.getWorkbook(xlsFile)>
		#xlsXml.getXML(workbook = workbook,startRow = form.startRow, maxRows = form.maxRows)#
	</cfif>

</cfif>

</cfoutput>